package com.becca.cake.core.excel.excelWriter.impl;

import java.io.IOException;
import java.io.OutputStream;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.becca.cake.core.common.BaseAction;
import com.becca.cake.core.excel.excelWriter.BaseExcelHandle;
import com.becca.cake.core.excel.excelWriter.ExcelInfo;
import com.becca.cake.core.excel.excelWriter.ExcelWriteUtils;

/**
 * Excel导出操作抽象
 * @author hejinguo
 * @version $Id: AbsExcelWrite.java, v 0.1 2014-4-23 下午10:07:50
 */
public abstract class AbsExcelWrite extends BaseAction implements BaseExcelHandle {
    private static Log logger      = LogFactory.getLog(AbsExcelWrite.class);
    public static int  defultRow   = 0;                                     //默认从第0行开始写入数据
    public static int  defultWidth = 13;                                    //默认的excel表格宽度

    public WritableWorkbook createWorkBook(OutputStream stream) {
        try {
            return Workbook.createWorkbook(stream);
        } catch (IOException e) {
            logger.error("创建Excel工作薄时IO流出现异常!", e);
        }
        return null;
    }

    public WritableSheet createSheet(WritableWorkbook workBook, String sheetName, int row) {
        if (workBook == null) {
            return null;
        } else {
            return workBook.createSheet(sheetName, row);
        }
    }

    public void setExcelTitle(WritableSheet sheet, String[][] reportTitle, int length)
                                                                                      throws WriteException {
        if (sheet == null || reportTitle == null) {
            return;
        }
        WritableFont wfontBigTitle = new WritableFont(WritableFont.ARIAL,
            ExcelWriteUtils.pointSizeOne, WritableFont.BOLD);// 设置大标题格式
        WritableFont wfontSmallTitle = new WritableFont(WritableFont.ARIAL,
            WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);// 设置小标题格式

        WritableCellFormat titleFormat = null;
        for (int i = 0; i < reportTitle.length; i++) {
            if (i == 0) {
                titleFormat = new WritableCellFormat(wfontBigTitle);
            } else if (i == 1) {
                titleFormat = new WritableCellFormat(wfontSmallTitle);
            }
            titleFormat.setBorder(Border.NONE, BorderLineStyle.NONE);
            if (reportTitle[i][1].equalsIgnoreCase("left")) {// 设置标题显示的位置
                titleFormat.setAlignment(Alignment.LEFT);
            } else if (reportTitle[i][1].equalsIgnoreCase("center")) {
                titleFormat.setAlignment(Alignment.CENTRE);
            } else if (reportTitle[i][1].equalsIgnoreCase("right")) {
                titleFormat.setAlignment(Alignment.RIGHT);
            }

            sheet.mergeCells(0, i, length - 1, i);// 设置标题所占用的单元格
            Label label = new Label(0, i, reportTitle[i][0], titleFormat);// 设置标题显示的位置
            sheet.addCell(label);
            label = null;
        }
    }

    public void createColumnHeader(WritableSheet sheet, String[] columnHeader, int row)
                                                                                       throws WriteException {
        if (sheet == null || columnHeader == null) {
            return;
        }
        WritableFont wfont = new WritableFont(WritableFont.createFont("宋体"),
            ExcelWriteUtils.pointSizetwo, WritableFont.NO_BOLD);
        WritableCellFormat columnFormat = new WritableCellFormat(wfont);
        columnFormat.setBorder(Border.NONE, BorderLineStyle.THIN, Colour.BLACK); // 线条
        columnFormat.setAlignment(Alignment.CENTRE);
        for (int i = 0; i < columnHeader.length; i++) {
            sheet.addCell(new Label(i, row, columnHeader[i], columnFormat));
        }
    }

    public void addDataToExcel(WritableSheet sheet, Object[][] data, int row) throws WriteException {
        if (sheet == null || data == null) {
            return;
        }
        int dataRow = data.length;// 计算行数
        int dataColumn = data[0].length;// 计算多少列数
        // 设置数据的样式
        WritableFont normalFont = new WritableFont(WritableFont.createFont("宋体"),
            ExcelWriteUtils.pointSizeThree, WritableFont.NO_BOLD);
        WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
        normalFormat.setBorder(Border.NONE, BorderLineStyle.NONE); // 线条
        normalFormat.setAlignment(Alignment.CENTRE);
        //doublel类型
        NumberFormat nf = new NumberFormat("#,##0.00"); //用于Number的格式
        WritableCellFormat doubleFormat = new WritableCellFormat(normalFont, nf);
        doubleFormat.setBorder(Border.NONE, BorderLineStyle.NONE); // 线条
        doubleFormat.setAlignment(Alignment.CENTRE);

        //long类型
        NumberFormat longNumber = new NumberFormat("#"); //用于Number的格式
        WritableCellFormat longFormat = new WritableCellFormat(normalFont, longNumber);
        longFormat.setBorder(Border.NONE, BorderLineStyle.NONE); // 线条
        longFormat.setAlignment(Alignment.CENTRE);
        for (int i = 0; i < dataRow; i++) {
            for (int j = 0; j < dataColumn; j++) {
                sheet.addCell(cell(j, i + row + 1, data[i][j], normalFormat, doubleFormat,
                    longFormat));
            }
        }
    }

    public void createFooter(WritableSheet sheet, String[][] columnFooter, int row)
                                                                                   throws WriteException {
        if (sheet == null || columnFooter == null) {
            return;
        }
        // 填充columnFooter字段及统计,一维是字串，二维是格式(跨度):columnFooter[x][2]
        WritableFont wfc = new WritableFont(WritableFont.ARIAL, ExcelWriteUtils.pointSizeThree,
            WritableFont.BOLD); // 设置字体对象
        WritableCellFormat wcfFC = new WritableCellFormat(wfc);
        if (columnFooter != null) {
            int columnFooterWidth = columnFooter.length;
            int tmpInt = 0, summergeNum = 0;
            for (int i = 0; i < columnFooterWidth; i++) {
                tmpInt = Integer.parseInt(columnFooter[i][1]);
                summergeNum += tmpInt;
                if (tmpInt > 1) {
                    sheet.mergeCells((summergeNum - tmpInt), (row + 1), (summergeNum - 1),
                        (row + 1)); // 合并单元格
                }
                Label lable = new Label((summergeNum - tmpInt), (row + 1), columnFooter[i][0],
                    wcfFC);
                sheet.addCell(lable);
                lable = null;
            }
        }
    }

    public WritableCell cell(int indexColumn, int indexRow, Object data,
                             WritableCellFormat normalFormat, WritableCellFormat doubleFormat,
                             WritableCellFormat longFormat) throws WriteException {
        WritableCell cell = null;
        if (data instanceof Double) {
            cell = (data != null) ? new jxl.write.Number(indexColumn, indexRow,
                Double.parseDouble(ExcelWriteUtils.strToDouble(data)), doubleFormat) : new Label(
                indexColumn, indexRow, null, normalFormat);
        } else if (data instanceof Long) {
            cell = (data != null) ? new jxl.write.Number(indexColumn, indexRow, Long.valueOf(data
                .toString()), longFormat) : new Label(indexColumn, indexRow, null, normalFormat);
        } else {
            cell = new Label(indexColumn, indexRow, data == null ? null : data.toString(),
                normalFormat);
        }
        return cell;
    }

    /**
     * 处理导出Excel
     * @param stream
     * @param info
     * @throws WriteException 
     * @throws IOException 
     */
    public void handleExcel(OutputStream stream, ExcelInfo info) throws WriteException, IOException {
        //标题所占的行数
        int titleRow = (info.getTitle() == null ? defultRow : info.getTitle().length);
        //获取list数据
        Object datas[][] = info.getDataStat();
        int rows = (datas == null) ? 0 : datas.length; // 得到数组行数
        int columns = (datas == null) ? 0 : datas[0].length; // 得到数组列数
        int widthLength[] = info.getWidth();
        if (columns == 0 && widthLength != null) {
            columns = widthLength.length;
        } else if (columns == 0) {
            columns = 1;
        }
        // 创建Excel工作薄
        WritableWorkbook workBook = this.createWorkBook(stream);
        // 建立报表表单
        WritableSheet sheet = this.createSheet(workBook, info.getSheetName(), 0);
        this.setCellWidth(sheet, columns, widthLength);
        // 设置Excel头信息
        this.setExcelTitle(sheet, info.getTitle(), columns);
        // 设置Excel列头信息
        this.createColumnHeader(sheet, info.getColumnHeader(), titleRow);
        // 向Excel表格中填充数据
        this.addDataToExcel(sheet, datas, titleRow);
        // 统计
        this.createFooter(sheet, info.getFooter(), rows + titleRow);
        workBook.write();
        workBook.close();
        stream.flush();
        stream.close();
    }

    /**
     * 设置列宽
     * @param sheet
     * @param columns
     * @param widthLength
     */
    public void setCellWidth(WritableSheet sheet, int columns, int widthLength[]) {
        if (widthLength == null) {
            widthLength = new int[columns];
            for (int j = 0; j < columns; j++) {
                widthLength[j] = defultWidth;
            }
        }
        // 设置列宽
        for (int i = 0; i < columns; i++) {
            sheet.setColumnView(i, widthLength[i]);
        }
    }
}
